﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Text.RegularExpressions;

namespace HY.Utility.DB
{
    public class MySQLDBHelper:IDisposable
    {
        private readonly static string _ConnectString;

        private MySqlConnection _Connection;
        private MySqlTransaction _Transaction;
        private int _CommandTimeout = 0;

        static MySQLDBHelper()
        {
            string connstr = System.Configuration.ConfigurationManager.AppSettings["HYConnectionString"];
            if (string.IsNullOrEmpty(connstr))
                throw new Exception("AppSettings不存在HYConnectionString");

            MySqlConnectionStringBuilder sb = new MySqlConnectionStringBuilder(connstr);
            sb.Pooling = true;
            sb.ConnectionTimeout = 60 * 60;//60分钟
            sb.MinimumPoolSize = 2;
            sb.MaximumPoolSize = 100;
            _ConnectString = sb.ConnectionString;
        }

        private MySqlCommand GetCommand(string sql)
        {
            var cmd = new MySqlCommand(sql, _Connection);
            cmd.CommandTimeout = 0;
            if (_Connection.State != ConnectionState.Open)
                _Connection.Open();
            return cmd;
        }

        public MySQLDBHelper()
        {
            _Connection = new MySqlConnection();
            _Connection.ConnectionString = _ConnectString;
            //_Connection.Open();
        }

        public void BeginTrans()
        {
            if (_Connection.State != ConnectionState.Open)
                _Connection.Open();
            _Transaction = _Connection.BeginTransaction();
        }

        public void CommitTrans()
        {
            if (_Transaction != null)
            {
                _Transaction.Commit();
                _Transaction.Dispose();
                _Transaction = null;
            }
        }

        public void AddParametersWithValues(MySqlCommand cmd, params object[] param)
        {
            var ms = Regex.Matches(cmd.CommandText, "@[a-zA-Z0-9_]+");
            if (ms != null)
            {
                if (param != null)
                {
                    if (ms.Count != param.Length)
                        throw new Exception("参数与参数值数量不相等");
                    var index = 0;
                    foreach (System.Text.RegularExpressions.Match m in ms)
                    {
                        cmd.Parameters.AddWithValue(m.Value, param[index]);
                    }
                }
            }
        }

        public  void ExecCmd(string sql,params object[] param)
        {
            using (var cmd = GetCommand(sql))
            {
                AddParametersWithValues(cmd, param);
                cmd.ExecuteNonQuery();
            }
        }

        public void AddParametersWithValues(MySqlCommand cmd, object param)
        {
            foreach (var p in param.GetType().GetProperties())
            {
                cmd.Parameters.AddWithValue("@" + p.Name, p.GetValue(param, null));
            }
        }

        public void ExecCmd(string sql, object param)
        {
            using (var cmd = GetCommand(sql))
            {
                AddParametersWithValues(cmd, param);
                cmd.ExecuteNonQuery();
            }
        }

        public List<T> Query<T>(string sql,object param=null) where T:new()
        {
            List<T> resultlist = new List<T>();
            var dt = GetTable(sql, param);
            foreach (DataRow row in dt.Rows)
            {
                var t = new T();
                foreach (var p in t.GetType().GetProperties())
                {
                    var index = dt.Columns.IndexOf(p.Name);
                    if (index != -1)
                    {
                        //if (row[index].GetType() == p.PropertyType)
                        //if (DBNull.Value != row[index])
                        if (DBNull.Value == row[index])
                            continue;
                        if (p.PropertyType.IsEnum)
                        {
                            p.SetValue(t, System.Enum.ToObject(p.PropertyType, row[index]), null);
                        }
                        else
                            p.SetValue(t, row[index], null);

                    }
                }
                resultlist.Add(t);
            }
            return resultlist;
        }

        public DataTable GetTable(string sql, object param = null)
        {
            var ds = new DataSet();
            using (var cmd = GetCommand(sql))
            {
                if (param != null)
                    AddParametersWithValues(cmd, param);
                using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
                {
                    adapter.Fill(ds);
                }
            }
            return ds.Tables[0];
        }

        /// <summary>
        /// 获得单行数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <returns></returns>
        public T Single<T>(string sql, object param = null) where T : new()
        {
            T result = default(T);
            using (var cmd = GetCommand(sql))
            {
                if (param != null)
                    AddParametersWithValues(cmd, param);
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        object[] values = new object[reader.FieldCount];
                        var fieldcount = reader.GetValues(values);
                        result = new T();
                        for (int i = 0; i < fieldcount; ++i)
                        {
                            foreach (var p in result.GetType().GetProperties())
                            {
                                if (p.Name == reader.GetName(i))
                                {
                                    if (DBNull.Value != values[i])
                                        p.SetValue(result, values[i], null);
                                }
                            }
                        }
                    }
                }
            }
            return result;
        }

        public object SingeValue(string sql, object param = null)
        {
            object result = null;
            using (var cmd = GetCommand(sql))
            {
                if (param != null)
                    AddParametersWithValues(cmd, param);
                result = cmd.ExecuteScalar();
            }
            return result;
        }

        void IDisposable.Dispose()
        {
            if (_Transaction != null)
            {
                _Transaction.Rollback();
                _Transaction.Dispose();
                _Transaction = null;
            }
            _Connection.Close();
            _Connection.Dispose();
            _Connection = null;
        }
    }
}
